<%@ page import="custom.*, java.util.*, java.sql.*, javax.sql.*, javax.naming.*"%>
<html>
<head>
<title>Applicant Information</title>
</head>
<body>
<h2>Applicant Information</h2> <p>
<%	int id = Integer.parseInt(request.getParameter("applicant_id"));
	InitialContext cxt = new InitialContext();
	DataSource ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/postgres");
	Connection conn = ds.getConnection();
	PreparedStatement stmt = conn.prepareStatement("SELECT first_name, middle_name, last_name, CC.country AS cc_country, CR.country AS cr_country, street, city, state, zip, area_code, phone, country_phone_code  FROM applicants AS A, countries AS CC, countries AS CR WHERE A.id = ? AND A.ccid = CC.id AND A.crid = CR.id");
	stmt.setInt(1, id);
	ResultSet rs = stmt.executeQuery();
	if(rs.next() ) {
%>
	<table>
    <tr><td>ID:</td><td><%=id %></td> </tr>
	<tr><td>First name:</td><td><%=rs.getString("first_name") %></td> </tr>
	<tr><td>Middle name:</td><td><%=rs.getString("middle_name") %></td> </tr>
	<tr><td>Last name:</td><td><%=rs.getString("last_name") %></td> </tr>
	<tr><td>Country of citizenship:</td><td><%=rs.getString("cc_country") %></td> </tr>
	<tr><td>Country of residence:</td><td><%=rs.getString("cr_country") %></td> </tr>
	<tr><td>Street:</td><td><%=rs.getString("street") %></td> </tr>
	<tr><td>City:</td><td><%=rs.getString("city") %></td> </tr>
	<tr><td>State:</td><td><%=rs.getString("state") %></td> </tr>
	<tr><td>Zip:</td><td><%=rs.getString("zip") %></td> </tr>
	<tr><td>Area code:</td><td><%=rs.getString("area_code") %></td> </tr>
	<tr><td>Phone number:</td><td><%=rs.getString("phone") %></td> </tr>
	<tr><td>Country phone code:</td><td><%=rs.getString("country_phone_code") %></td> </tr>
	</table>
	<h3> Degree(s):</h3> <p>
	
<%  }
	// get degrees info
	stmt = conn.prepareStatement("SELECT university, major, graduation_date, gpa, degree_type FROM applicants AS A, degrees AS D, majors AS M, universities AS U WHERE A.id = ? AND A.id = D.applicant_id AND D.university_id = U.id AND D.major_id = M.id ");
	stmt.setInt(1, id);
	rs = stmt.executeQuery();
	while(rs.next() ) {
%>
		<table>
		<tr><td>University:</td><td><%=rs.getString("university") %></td> </tr>
		<tr><td>Discipline:</td><td><%=rs.getString("major") %></td> </tr>
		<tr><td>Graduation date:</td><td><%=rs.getString("graduation_date") %></td> </tr>
		<tr><td>GPA:</td><td><%=rs.getString("gpa") %></td> </tr>
		<tr><td>Degree type:</td><td><%=rs.getString("degree_type") %></td> </tr>
		</table> <p>
<% 	}
	stmt = conn.prepareStatement("SELECT specialization FROM applicants AS A, specializations AS S WHERE A.id = ? AND A.specialization_id = S.id");
	stmt.setInt(1, id);
	rs = stmt.executeQuery();
	if( rs.next() ) {
%>
	<p>Specialization: <%=rs.getString("specialization")%>
<% }
	rs.close();
	stmt.close();
	conn.close();
%>

</body>
</html>
